using System;
using System.Collections.Generic;
using System.Linq;
using Dapper;
using MediatR;
using Microsoft.Extensions.Logging;
using RBAC.Domain.Dto.Output;
using RBAC.Domain.WarehouseManagement;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.PurchasingManagement.PurchaseInvoicefolder;

namespace RBAC.Read.Api.Application.Handler.ERP.PurchasingManagement.PurchaseInvoicefolder
{
    /// <summary>
    /// 采购发票列表查询（Dapper，两表联查）
    /// </summary>
    public class GetPurchaseInvoiceCommandHandler : IRequestHandler<GetPurchaseInvoiceCommands, APIPaging<PurchaseInvoiceListDto>>
    {
        private readonly ILogger<GetPurchaseInvoiceCommandHandler> _logger;
        private readonly IBaseRepository<PurchaseInvoice> _invoiceRepository;

        public GetPurchaseInvoiceCommandHandler(
            ILogger<GetPurchaseInvoiceCommandHandler> logger,
            IBaseRepository<PurchaseInvoice> invoiceRepository)
        {
            _logger = logger;
            _invoiceRepository = invoiceRepository;
        }

        public Task<APIPaging<PurchaseInvoiceListDto>> Handle(GetPurchaseInvoiceCommands request, CancellationToken cancellationToken)
        {
            var res = new APIPaging<PurchaseInvoiceListDto>();
            try
            {
                // 1) 统计总数（只基于发票表过滤，未含物料过滤）
                var whereParts = new List<string> { "pi.IsDeleted = 0" };
                var param = new DynamicParameters();

                if (!string.IsNullOrWhiteSpace(request.DocumentCode))
                {
                    whereParts.Add("pi.DocumentCode LIKE @DocumentCode");
                    param.Add("DocumentCode", $"%{request.DocumentCode}%");
                }
                if (request.SupplierId.HasValue)
                {
                    whereParts.Add("pi.SupplierId = @SupplierId");
                    param.Add("SupplierId", request.SupplierId.Value);
                }
                if (request.InvoiceDateStart.HasValue)
                {
                    whereParts.Add("pi.InvoiceDate >= @InvoiceDateStart");
                    param.Add("InvoiceDateStart", request.InvoiceDateStart.Value);
                }
                if (request.InvoiceDateEnd.HasValue)
                {
                    whereParts.Add("pi.InvoiceDate <= @InvoiceDateEnd");
                    param.Add("InvoiceDateEnd", request.InvoiceDateEnd.Value);
                }

                var whereSql = whereParts.Count > 0 ? (" WHERE " + string.Join(" AND ", whereParts)) : string.Empty;

                var countSql = "SELECT COUNT(1) FROM PurchaseInvoice pi" + whereSql;
                var total = _invoiceRepository.Query<int>(countSql, param).FirstOrDefault();
                res.TotalCount = total;

                if (total == 0)
                {
                    res.Code = APIEnums.Success;
                    res.PageDatas = new List<PurchaseInvoiceListDto>();
                    res.Msg = "获取采购发票列表成功";
                    return Task.FromResult(res);
                }

                // 2) 获取分页的发票ID集合
                var offset = (request.PageIndex - 1) * request.PageSize;
                if (offset < 0) offset = 0;
                param.Add("Offset", offset);
                param.Add("PageSize", request.PageSize);

                var idSql = "SELECT pi.Id FROM PurchaseInvoice pi" + whereSql + " ORDER BY pi.Id DESC LIMIT @Offset, @PageSize";
                var pageIds = _invoiceRepository.Query<int>(idSql, param).ToList();

                if (pageIds.Count == 0)
                {
                    res.Code = APIEnums.Success;
                    res.PageDatas = new List<PurchaseInvoiceListDto>();
                    res.Msg = "获取采购发票列表成功";
                    return Task.FromResult(res);
                }

                // 3) 拉取该页所有发票的详细信息 + 联查物料（基于 FIND_IN_SET）
                var dataSql = @"SELECT 
                                pi.Id,
                                pi.DocumentCode,
                                pi.BusinessType,
                                pi.SupplierId,
                                pi.InvoiceDate,
                                pi.InvoiceUpload,
                                pi.DocumentDate,
                                pi.Operator,
                                pi.TaxNumber,
                                pi.RedBlue,
                                pi.XMLFormat,
                                pi.DocumentStatus,
                                pi.Auditor,
                                pi.InvoiceType,
                                pi.InvoiceAmount,
                                pi.OFDFormat,
                                pi.BusinessStatus,
                                pi.ReviewDate,
                                pi.InvoiceCode,
                                pi.TaxAmount,
                                pi.InvoiceStatus,
                                pi.InvoiceNumber,
                                pi.TotalTax,
                                pi.Remarks,
                                pm.PurchaseInvoiceMaterialsId AS Mat_PurchaseInvoiceMaterialsId,
                                pm.PurchaseInvoiceId AS Mat_PurchaseInvoiceId,
                                pm.PurchaseOrderNumber AS Mat_PurchaseOrderNumber,
                                pm.Warehouse AS Mat_Warehouse,
                                pm.InboundOrderNumber AS Mat_InboundOrderNumber,
                                pm.MaterialCode AS Mat_MaterialCode,
                                pm.MaterialName AS Mat_MaterialName,
                                pm.ModelSpecifications AS Mat_ModelSpecifications,
                                pm.MainMeasurement AS Mat_MainMeasurement,
                                pm.`Number` AS Mat_Number,
                                pm.UnitPrice AS Mat_UnitPrice,
                                pm.Amount AS Mat_Amount,
                                pm.TaxRate AS Mat_TaxRate,
                                pm.TaxAmount AS Mat_TaxAmount,
                                pm.PriceTaxTotal AS Mat_PriceTaxTotal,
                                pm.IsDel AS Mat_IsDel
                            FROM PurchaseInvoice pi
                            LEFT JOIN ProcurementMaterials pm 
                                ON pm.PurchaseInvoiceId = pi.Id
                            WHERE pi.Id IN @Ids
                            ORDER BY pi.Id DESC, pm.PurchaseInvoiceMaterialsId ASC";

                var rows = _invoiceRepository.Query<dynamic>(dataSql, new { Ids = pageIds });

                // 4) 按发票聚合为 DTO
                var grouped = rows.GroupBy(r => (int)r.Id)
                    .Select(g =>
                    {
                        var first = g.First();
                        var dto = new PurchaseInvoiceListDto
                        {
                            Id = (int)first.Id,
                            DocumentCode = (string?)first.DocumentCode ?? string.Empty,
                            BusinessType = (string?)first.BusinessType ?? string.Empty,
                            SupplierId = (long?)((object)first.SupplierId) ?? 0L,
                            InvoiceDate = (DateTime?)((object)first.InvoiceDate) ?? default(DateTime),
                            InvoiceUpload = (string?)first.InvoiceUpload ?? string.Empty,
                            DocumentDate = (string?)first.DocumentDate ?? string.Empty,
                            Operator = (string?)first.Operator ?? string.Empty,
                            TaxNumber = (string?)first.TaxNumber ?? string.Empty,
                            RedBlue = (string?)first.RedBlue ?? string.Empty,
                            XMLFormat = (string?)first.XMLFormat ?? string.Empty,
                            DocumentStatus = (string?)first.DocumentStatus ?? string.Empty,
                            Auditor = (string?)first.Auditor ?? string.Empty,
                            InvoiceType = (string?)first.InvoiceType ?? string.Empty,
                            InvoiceAmount = (string?)first.InvoiceAmount ?? string.Empty,
                            OFDFormat = (string?)first.OFDFormat ?? string.Empty,
                            BusinessStatus = (string?)first.BusinessStatus ?? string.Empty,
                            ReviewDate = (DateTime?)((object)first.ReviewDate) ?? default(DateTime),
                            InvoiceCode = (string?)first.InvoiceCode ?? string.Empty,
                            TaxAmount = (string?)first.TaxAmount ?? string.Empty,
                            InvoiceStatus = (string?)first.InvoiceStatus ?? string.Empty,
                            InvoiceNumber = (string?)first.InvoiceNumber ?? string.Empty,
                            TotalTax = (string?)first.TotalTax ?? string.Empty,
                            Remarks = (string?)first.Remarks ?? string.Empty,
                            Materials = new List<PurchaseInvoiceMaterialItemDto>()
                        };

                        foreach (var r in g)
                        {
                            // 没有关联物料时，Mat_* 为空，跳过
                            if (r.Mat_PurchaseInvoiceMaterialsId == null) continue;
                            dto.Materials.Add(new PurchaseInvoiceMaterialItemDto
                            {
                                PurchaseInvoiceId = (long?)((object)r.Mat_PurchaseInvoiceId) ?? 0L,
                                PurchaseInvoiceMaterialsId = (int?)((object)r.Mat_PurchaseInvoiceMaterialsId) ?? 0,
                                PurchaseOrderNumber = (string?)r.Mat_PurchaseOrderNumber ?? string.Empty,
                                Warehouse = (string?)r.Mat_Warehouse ?? string.Empty,
                                InboundOrderNumber = (string?)r.Mat_InboundOrderNumber ?? string.Empty,
                                MaterialCode = (string?)r.Mat_MaterialCode ?? string.Empty,
                                MaterialName = (string?)r.Mat_MaterialName ?? string.Empty,
                                ModelSpecifications = (string?)r.Mat_ModelSpecifications ?? string.Empty,
                                MainMeasurement = (string?)r.Mat_MainMeasurement ?? string.Empty,
                                Number = r.Mat_Number?.ToString(),
                                UnitPrice = (string?)r.Mat_UnitPrice ?? string.Empty,
                                Amount = (string?)r.Mat_Amount ?? string.Empty,
                                TaxRate = (string?)r.Mat_TaxRate ?? string.Empty,
                                TaxAmount = (string?)r.Mat_TaxAmount ?? string.Empty,
                                PriceTaxTotal = (string?)r.Mat_PriceTaxTotal ?? string.Empty,
                                IsDel = (bool?)((object)r.Mat_IsDel) ?? false
                            });
                        }

                        return dto;
                    })
                    .OrderByDescending(x => x.Id)
                    .ToList();

                res.Code = APIEnums.Success;
                res.PageDatas = grouped;
                res.Msg = "获取采购发票列表成功";
            }
            catch (Exception ex)
            {
                res.Code = APIEnums.Error;
                res.Msg = "获取采购发票列表失败";
                _logger.LogError(ex, "获取采购发票列表失败: {Message}", ex.Message);
            }

            return Task.FromResult(res);
        }
    }
}
